﻿using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Xml.Linq;
using OfficeOpenXml;

namespace ExportExcel.Tool
{
    public static class Tool_ExportExcel
    {
        /// <summary>
        /// 处理txt并到处Excel
        /// </summary>
        /// <param name="sourcePath">需要处理的文件</param>
        /// <param name="temFileName">模板文件</param>
        public static void ExportExcel()
        {
            Console.WriteLine("请将需要处理的txt文件命名为1.xml并放入【1_来源文件】文件夹。");
            Console.WriteLine("请将模板Excel放入【2_模板】,并命名为【1.xlsx】。");
            Console.WriteLine("准备好后，按任意按键开始处理。");
            Console.ReadKey();

            //1、获取xml并处理成List
            if(!Directory.Exists(Directory.GetCurrentDirectory() + @"\1_来源文件"))
            {
                Console.WriteLine("错误！来源文件不存在");
                Console.ReadKey();
                return;
            }
            string[] filePaths = Directory.GetFiles(Directory.GetCurrentDirectory()+@"\1_来源文件");
            if (filePaths.Length<=0)
            {
                Console.WriteLine("错误！来源文件不存在");
                Console.ReadKey();
                return;
            }
            string temFilePath = Directory.GetCurrentDirectory() + @"\2_模板\1.xlsx";
            if(!File.Exists(temFilePath))
            {
                Console.WriteLine("错误！模板文件不存在");
                Console.ReadKey();
                return;
            }    
            string outPaht = Directory.GetCurrentDirectory() + @"\3_输出文件";
            if (!Directory.Exists(outPaht))
                Directory.CreateDirectory(outPaht);

            foreach (var filePath in filePaths)
            {
                string OutFilePath = outPaht + @"\"+getFileName(filePath) + ".xlsx";
                FileInfo newFile = new FileInfo(OutFilePath);
                if (newFile.Exists)
                {
                    newFile.Delete();
                    newFile = new FileInfo(OutFilePath);
                }

                //读取模板Excel
                FileInfo temFile = new FileInfo(temFilePath);
                //处理成List
                Console.WriteLine("--------------------开始解析文件:"+filePath+ "--------------------");
                List<package_T> list = getList(filePath);
                
                using (ExcelPackage package = new ExcelPackage(temFile))
                {
                    ExcelWorksheet worksheet1 = package.Workbook.Worksheets["表信息"];
                    ExcelWorksheet worksheet2 = package.Workbook.Worksheets["字段信息"];

                    #region 处理表名
                    int rowIndex = 3;
                    try
                    {
                        int packSort = 1;
                        foreach (var e in list)
                        {
                            e.sort = packSort;
                            packSort++;
                            int tableSort = 1;
                            foreach (var f in e.tables)
                            {
                                worksheet1.Cells[rowIndex, 1].Value = e.sort;
                                worksheet1.Cells[rowIndex, 2].Value = e.Name;
                                worksheet1.Cells[rowIndex, 3].Value = e.Code;
                                worksheet1.Cells[rowIndex, 4].Value = e.Comment;
                                f.sort = tableSort;
                                tableSort++;
                                worksheet1.Cells[rowIndex, 5].Value = f.sort;
                                worksheet1.Cells[rowIndex, 6].Value = f.Code;
                                worksheet1.Cells[rowIndex, 7].Value = f.Name2;
                                rowIndex++;
                            }
                        }
                    }
                    catch (Exception ex)
                    {
                        Console.WriteLine("导出表信息时错误，详查日志。");
                    }

                    #endregion

                    #region 处理字段名

                    rowIndex = 3;
                    try
                    {
                        foreach (var e in list)
                        {
                            foreach (var f in e.tables)
                            {
                                int columnSort = 1;
                                foreach (var g in f.Columns)
                                {
                                    worksheet2.Cells[rowIndex, 1].Value = e.sort;
                                    worksheet2.Cells[rowIndex, 2].Value = e.Name;
                                    worksheet2.Cells[rowIndex, 3].Value = e.Code;
                                    worksheet2.Cells[rowIndex, 4].Value = e.Comment;

                                    worksheet2.Cells[rowIndex, 5].Value = f.sort;
                                    worksheet2.Cells[rowIndex, 6].Value = f.Code;
                                    worksheet2.Cells[rowIndex, 7].Value = f.Name2;
                                    g.sort = columnSort;
                                    columnSort++;
                                    worksheet2.Cells[rowIndex, 8].Value = g.sort;
                                    worksheet2.Cells[rowIndex, 9].Value = g.Code;
                                    worksheet2.Cells[rowIndex, 10].Value = g.Name;
                                    worksheet2.Cells[rowIndex, 11].Value = g.DataType;
                                    worksheet2.Cells[rowIndex, 12].Value = g.DefaultValue;
                                    worksheet2.Cells[rowIndex, 13].Value = g.Length;
                                    rowIndex++;
                                }
                            }
                        }
                    }
                    catch (Exception ex)
                    {
                        Console.WriteLine("导出字段信息时错误，详查日志。");
                    }

                    #endregion

                    package.SaveAs(newFile);
                    Console.WriteLine("输出文件："+newFile.FullName);
                }
            }

            Console.WriteLine("处理完成，请查看路径："+ outPaht);
        }

        //xml转List
        private static List<package_T> getList(string sourcePath)
        {
            List<package_T> packages = new List<package_T>();

            string str = File.ReadAllText(sourcePath);

            //去掉多余的父项
            str = str.Substring(str.IndexOf("<c:Packages>"), 
                str.IndexOf("</c:Packages>")+("</c:Packages>".Length) -str.IndexOf("<c:Packages>"));

            str = @"<?xml version=""1.0"" encoding =""UTF-8""?>" + str;
            //去掉多余的字段
            str = str.Replace("<a:", "<").Replace("<c:", "<").Replace("<o:", "<");
            str = str.Replace("</a:", "</").Replace("</c:", "</").Replace("</o:", "</");

            XElement xml = XElement.Parse(str);
            Console.WriteLine("开始解析packages...");
            var packagesXml = from ps in xml.Elements()
                              select ps;
            foreach (var p in packagesXml)
            {
                //1、获取分部
                package_T pT = new package_T()
                {
                    ObjectID = p.Element("ObjectID")?.Value,
                    Code = p.Element("Code")?.Value,
                    Name = p.Element("Name")?.Value??"",
                    Comment = p.Element("Comment")?.Value??""
                };
                Console.WriteLine("--解析出package:"+pT.Name);
                packages.Add(pT);
                //2、获取分部下所有表
                pT.tables = new List<table_T>();
                Console.WriteLine("----开始解析Tables...");
                var tablesXml = p.Element("Tables");
                if (tablesXml == null)
                    continue;
                var tables = from ts in tablesXml.Elements()
                                .Where(c => c.Element("TargetID")?.Value == null) //排除Shortcut
                             select ts;
                foreach (var t in tables)
                {
                    if (t.Element("Columns")?.Value == null)
                        continue;
                    table_T table = new table_T()
                    {
                        ObjectID = t.Element("ObjectID")?.Value,
                        Code = t.Element("Code")?.Value.ToLower(),
                        Name = t.Element("Name")?.Value,
                    };
                    Console.WriteLine("----解析出table:" + table.Name);
                    pT.tables.Add(table);
                    //3、获取表下所有字段
                    table.Columns = new List<column_T>();
                    Console.WriteLine("------开始解析Columns...");
                    var columnsXml = t.Element("Columns");
                    var columns = from cs in columnsXml.Elements() 
                                  select cs;
                    foreach (var c in columns)
                    {
                        column_T column = new column_T()
                        {
                            Name = c.Element("Name")?.Value,
                            Code = c.Element("Code")?.Value.ToLower(),
                            DataType = c.Element("DataType")?.Value,
                            DefaultValue = c.Element("DefaultValue")?.Value ?? "",
                            Length = c.Element("Length")?.Value ?? ""
                        };
                        Console.WriteLine("------解析出Column:"+column.Name);
                        table.Columns.Add(column);
                    }
                }
            }

            return packages.OrderBy(c=>c.Name).ToList();
        }

        //根据文件路径获取文件名
        private static string getFileName(string path)
        {
            path = path.Substring(path.LastIndexOf(@"\") + 1, path.LastIndexOf(@".") - path.LastIndexOf(@"\") -1);
            return path;
        }

    }

    #region 临时类

    public class package_T
    {
        public int sort { get; set; }
        public string ObjectID { get; set; }

        public string Name { get; set; }

        public string Code { get; set; }

        public string Comment { get; set; }

        public List<table_T> tables { get; set; }
    }

    public class table_T
    {
        public int sort { get; set; }
        public string ObjectID { get; set; }
        public string Name { get; set; }
        public string Code { get; set; }
        public string Name2
        {
            get
            {
                   return Name= Name.Replace(Code,"").Replace(Code.ToUpper(),""); 
            }
        }
        public List<column_T> Columns { get; set; }
    }

    public class column_T
    {
        public int sort { get; set; }
        public string Code { get; set; }
        public string Name { get; set; }
        public string DataType { get; set; }
        /// <summary>
        /// 必填
        /// </summary>
        public string Mandatory { get; set; }
        public string DefaultValue { get; set; }
        public string Length { get; set; }
    }

    #endregion
}
